10.0: SQLite Primer

Contents:

This course assumes that you are familiar with databases in general, SQL databases in particular, and the SQL language used to interact with them. This chapter is a refresher and quick reference only.

SQL databases

  • Store data in tables of rows and columns.
  • The intersection of a row and column is called a field.
  • Fields contain data, references to other fields, or references to other tables.
  • Rows are identified by unique IDs.
  • Columns are identified by names that are unique per table.

Think of it as a spreadsheet with rows, columns, and cells, where cells can contain data, references to other cells, and links to other sheets.

SQLite

SQLite is a software library that implements SQL database engine that is:

  • self-contained (requires no other components)
  • serverless (requires no server backend)
  • zero-configuration (does not need to be configured for your application)
  • transactional (changes within a single transaction in SQLite either occur completely or not at all)

SQLite is the most widely deployed database engine in the world. The source code for SQLite is in the public domain.

For details of the SQLite database, see the SQLite website.

Example table

SQLite stores data in tables.

Assume the following:

  • A database DATABASE_NAME
  • A table WORD_LIST_TABLE
  • Columns for _id, word, and description

After inserting the words "alpha" and "beta", where alpha has two definitions, the table might look like this:

DATABASE_NAME

WORD_LIST_TABLE

_id

word

definition

1

"alpha"

"first letter"

2

"beta"

"second letter"

3

"alpha"

"particle"

You can find what's in a specific row using the _id, or you can retrieve rows by formulating queries that select rows from the table be specifying constraints. You use the SQL query language discussed below to create queries.

Transactions

A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction.

All changes within a single transaction in SQLite either occur completely or not at all, even if the act of writing the change out to the disk is interrupted by

  • a program crash,
  • an operating system crash, or
  • a power failure.

Examples of transactions:

  • Transferring money from a savings account to a checking account.
  • Entering a term and definition into dictionary.
  • Committing a changelist to the master branch.

ACID

  • Atomicity. Either all of its data modifications are performed, or none of them are performed.
  • Consistency. When completed, a transaction must leave all data in a consistent state.
  • Isolation. Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state.
  • Durability. After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

More on transactions.

Query language

You use a special SQL query language to interact with the database. Queries can be very complex, but the basic operations are

  • inserting rows
  • deleting rows
  • updating values in rows
  • retrieving rows that meet given criteria

On Android, the database object provides convenient methods for inserting, deleting, and updating the database. You only need to understand SQL for retrieving data.

Full description of the query language.

Query structure

A SQL query is highly structured and contains the following basic parts:

  • SELECT word, description FROM WORD_LIST_TABLE WHERE word="alpha"

Generic version of sample query:

  • SELECT columns FROM table WHERE column="value"

Parts:

  • SELECT columns—select the columns to return. Use * to return all columns.
  • FROM table—specify the table from which to get results.
  • WHERE—keyword for conditions that have to be met.
  • column="value"—the condition that has to be met.
    • common operators: =, LIKE, <, >
  • AND, OR—connect multiple conditions with logic operators.
  • ORDER BY—omit for default order, or specify ASC for ascending, DESC for descending.
  • LIMIT is a very useful keyword if you want to only get a limited number of results.

Sample queries

1

SELECT * FROM WORD_LIST_TABLE

Get the whole table.

2

SELECT word, definition FROM WORD_LIST_TABLE WHERE _id > 2

Returns

[["alpha", "particle"]]

3

SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha" AND definition LIKE "%art%"

Return the id of the word alpha with the substring "art" in the definition.

[["3"]]

4

SELECT * FROM WORD_LIST_TABLE ORDER BY word DESC LIMIT 1

Sort in reverse and get the first item. This gives you the last item per sort order. Sorting is by the first column, in this case, the _id.

[["3", "alpha", "particle"]]

5

SELECT * FROM WORD_LIST_TABLE LIMIT 2,1

Returns 1 item starting at position 2. Position counting starts at 1 (not zero!). Returns [["2", "beta", "second letter"]]

You can practice creating and querying databases at this Fiddle website and HeadFirst Labs.

Queries for Android SQLite

You can send queries to the SQLite database of the Android system as raw queries or as parameters.

The following table shows how the first two queries from above would look as raw queries.

1

String query = "SELECT * FROM WORD_LIST_TABLE";

rawQuery(query, null);

2

query = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? ";

String[] selectionArgs = new String[]{"2"}

rawQuery(query, selectionArgs) ;

Here's a query showing how to fill in the arguments:

SELECT * FROM WORD_LIST_TABLE
WHERE word="alpha"
ORDER BY word ASC
LIMIT 2,1;

Returns:

[["alpha", "particle"]]
String table = "WORD_LIST_TABLE"
String[] columns = new String[]{"*"};
String selection = "word = ?"
String[] selectionArgs = new String[]{"alpha"};
String groupBy = null;
String having = null;
String orderBy = "word ASC"
String limit = "2,1"

query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

Note that in real code, you wouldn't create variables for null values. See the SQLiteDatabase documentation for versions of this method with different parameters.

Cursors

Queries always return a Cursor object. A Cursor is an object interface that provides random read-write access to the result set returned by a database query. It points to the first element in the result of the query.

A cursor is a pointer into a row of structured data. You can think of it as a pointer to table rows.

The Cursor class provides methods for moving the cursor through that structure, and methods to get the data from the columns of each row.

When a method returns a Cursor object, you iterate over the result, extract the data, do something with the data, and finally close the cursor to release the memory.

You will learn more about cursors in the following chapters.

Learn more

results matching ""

    No results matching ""